#!/usr/bin/python
#coding=utf-8

from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
# from common import df_operator as op, db_utils as util
from common import db_utils as util
import sys


dtgC25s = """
    CREATE TABLE BGTARAS1.PREQ314_DTG_C25S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        FURNACE_PRESS_ATC DECIMAL(24,6),
        FURNACE_PRESS_EN_SEAL DECIMAL(24,6),
        FURNACE_PRESS_EX_SEAL DECIMAL(24,6),
        FURNACE_PRESS_RJC DECIMAL(24,6),
        FURNACE_PRESS_RTF DECIMAL(24,6),
        FURNACE_PRESS_SF DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""



def createDTG_C25S(start_time,end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ314_DTG_C25S' AND TABSCHEMA='BGTARAS1'"""
    sql_result = util.query(conn_rds, sql=sql)
    if sql_result.__len__() > 0 :
        conn_rds.execute("drop table BGTARAS1.PREQ314_DTG_C25S")
    else:
        conn_rds.execute(dtgC25s)

    sql = """WITH
            TMP AS
            (
                SELECT
                    A.UNIT_CODE,
                    A.PRODUCE_TIME,
                    A.CLASS,
                    A.SHIFT,
                    A.PROD_COILNO,
                    A.ENTRY_COILNO,
                    CONCAT(B.START_TIME_DATE, B.START_TIME_TIME) AS START_TIME,
                    CONCAT(B.END_TIME_DATE, B.END_TIME_TIME)     AS END_TIME
                FROM
                    BGTARAS1.T_ADS_FACT_SICB_CD0201 A,
                    BGTAROQ314.DTG_DCDT B
                WHERE
                    A.UNIT_CODE='Q314'
                AND A.COST_CENTER='MIHF'
                AND B.PRD_COILNO=a.PROD_COILNO
                AND a.PRODUCE_TIME BETWEEN """+start_time+""" AND """+end_time+"""
            )
        SELECT
            A.UNIT_CODE,
            A.PRODUCE_TIME,
            A.CLASS,
            A.SHIFT,
            A.PROD_COILNO,
            A.ENTRY_COILNO,
            AVG(CAST(C.FURNACE_PRESS_ATC AS DOUBLE)/10)     AS FURNACE_PRESS_ATC,
            AVG(CAST(C.FURNACE_PRESS_EN_SEAL AS DOUBLE)/10) AS FURNACE_PRESS_EN_SEAL,
            AVG(CAST(C.FURNACE_PRESS_EX_SEAL AS DOUBLE)/10) AS FURNACE_PRESS_EX_SEAL,
            AVG(CAST(C.FURNACE_PRESS_RJC AS DOUBLE)/10)     AS FURNACE_PRESS_RJC,
            AVG(CAST(C.FURNACE_PRESS_RTF AS DOUBLE)/10)     AS FURNACE_PRESS_RTF,
            AVG(CAST(C.FURNACE_PRESS_SF AS DOUBLE)/10)      AS FURNACE_PRESS_SF
        FROM
            TMP A
        LEFT JOIN
            BGTAROQ314.DTG_C25S c
        ON
            C.UP_DATE BETWEEN A.START_TIME AND A.END_TIME
        GROUP BY
            A.UNIT_CODE,
            A.PRODUCE_TIME,
            A.CLASS,
            A.SHIFT,
            A.PROD_COILNO,
            A.ENTRY_COILNO
        """
    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ314_DTG_C25S", "BGTARAS1")


def PreQ314(start_time, end_time):
    global conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    createDTG_C25S(start_time, end_time)
    conn_rds.close()
